﻿--TẠO DATA BẢNG TRADE
IF EXISTS
      (SELECT *
         FROM TEMPDB.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME like '%#CIF_TRADE%')
        DROP TABLE #CIF_TRADE

SELECT DISTINCT CUST_ID
INTO #CIF_TRADE
 FROM (SELECT CUST_ID AS CUST_ID FROM SERVER12.TRANGLT9.[DBO].[DOANH_SO_BAO_LANH] 
 WHERE CUST_ID IN (SELECT CIF FROM TBL_CUSTOMER)
 UNION ALL
 SELECT DISTINCT A.CIF AS CUST_ID FROM SERVER12.ANHCP.[DBO].[DOANH_SO_TTR_LC_DP] A
WHERE CIF IN (SELECT CIF FROM TBL_CUSTOMER))A

 --SELECT * FROM #CIF_TRADE
 --DROP TABLE #CIF_TRADE
 INSERT #CIF_TRADE
SELECT DISTINCT A.CIF FROM SERVER12.ANHCP.[DBO].[DOANH_SO_TTR_LC_DP] A
WHERE CIF IN (SELECT CIF FROM TBL_CUSTOMER) AND NOT EXISTS (SELECT *
                   FROM   #CIF_TRADE OD
                   WHERE  A.CIF = OD.CUST_ID)

--INSERT #CIF_TRADE
--SELECT DISTINCT CUST_ID FROM QUYNHNN3.TRADE2.[DBO].[DOANH_SO_BAO_LANH_S12_OLD] A
--WHERE CUST_ID IN (SELECT CIF FROM TBL_CUSTOMER) AND NOT EXISTS (SELECT *
--                   FROM   #CIF_TRADE OD
--                   WHERE  A.CUST_ID = OD.CUST_ID)


 --DƯ NỢ CHI TIẾT TRADE LOAN (CHO VAY TÀI TRỢ THƯƠNG MẠI)
 DELETE FROM TBL_DU_TRADE_LOAN_DETAIL

INSERT TBL_DU_TRADE_LOAN_DETAIL
SELECT CUSTOMER_ID AS CIF, CUSTOMER_NAME AS CUST_NAME, SUM(SO_DU) AS SUM_SO_DU,PRODUCT,BRANCH_CODE, BRANCH_NAME_SME, ZONE_ID_SME
--INTO TBL_DU_TRADE_LOAN_DETAIL
FROM TBL_DU_TRADE_LOAN_DAILY
GROUP BY CUSTOMER_ID, CUSTOMER_NAME,PRODUCT,BRANCH_CODE, BRANCH_NAME_SME, ZONE_ID_SME
ORDER BY CIF

INSERT #CIF_TRADE
SELECT DISTINCT CIF
FROM TBL_DU_TRADE_LOAN_DETAIL A
WHERE NOT EXISTS (SELECT *
                FROM   #CIF_TRADE OD
                WHERE  A.CIF = OD.CUST_ID) AND A.CIF IN (SELECT CIF FROM TBL_CUSTOMER)
INSERT #CIF_TRADE
SELECT DISTINCT CIF
FROM TBL_TTQT_DAILY A
WHERE NOT EXISTS (SELECT *
                FROM   #CIF_TRADE OD
                WHERE  A.CIF = OD.CUST_ID) AND CIF IN (SELECT CIF FROM TBL_CUSTOMER)

INSERT #CIF_TRADE
SELECT DISTINCT CUST_ID
FROM DOANH_SO_BAO_LANH A
WHERE NOT EXISTS (SELECT *
                FROM   #CIF_TRADE OD
                WHERE  A.CUST_ID = OD.CUST_ID) AND CUST_ID IN (SELECT CIF FROM TBL_CUSTOMER)

INSERT #CIF_TRADE
 SELECT CUSTOMER_ID FROM SERVER12.ANHCP.DBO.KH_TRADE_MOI A
 WHERE NOT EXISTS (SELECT *
                FROM   #CIF_TRADE OD
                WHERE  A.CUSTOMER_ID = OD.CUST_ID)
				   --drop table TBL_CM_TRADE
DELETE FROM TBL_CM_TRADE
INSERT TBL_CM_TRADE
	SELECT Format(a.CUST_ID, '####')  as cif, B.CUS_NAME as cust_name, 0 AS BAL_TTQT, 0 AS BAL_BL, 0 as DU_NO_TRADE_LOAN,'N' AS CHUOI,B.DAO,B.DAO_NAME,C.BRANCH_CODE_SME,C.BRANCH_NAME_SME, C.REGION, 0 AS NEW_CUST_STATUS, B.INDUSTRY_NAME, B.CUS_OPEN_DATE , B.INDUSTRY_NAME_EN, B.BRANCH_ID
	--into TBL_CM_TRADE
	FROM #CIF_TRADE A, TBL_CUSTOMER B, TBL_BRANCH C
	WHERE A.CUST_ID=B.CIF AND B.BRANCH_ID=C.BRANCH_ID
	--select * from TBL_DU_TRADE_LOAN_DETAIL
	--TẠO DATA DƯ NỢ TRADE LOAN
UPDATE TBL_CM_TRADE
SET DU_NO_TRADE_LOAN = A.DU_NO_TRADE_LOAN
FROM (SELECT CIF, CUST_NAME, 0 AS BAL_TTQT, 0 AS BAL_BL, SUM(SUM_SO_DU) AS DU_NO_TRADE_LOAN,'N' AS CHUOI,NULL AS DAO, NULL AS DAO_NAME,BRANCH_CODE, BRANCH_NAME_SME, ZONE_ID_SME
FROM TBL_DU_TRADE_LOAN_DETAIL
GROUP BY CIF, CUST_NAME,BRANCH_CODE, BRANCH_NAME_SME, ZONE_ID_SME) AS A, TBL_CM_TRADE B
WHERE A.CIF=B.CIF
	
UPDATE TBL_CM_TRADE
SET BAL_TTQT = A.BAL_TTQT
FROM 
(SELECT CIF, CUSTOMER_NAME, SUM([TRANSACTION]) AS BAL_TTQT, 0 AS BAL_BL, 0 AS DU_NO_TRADE_LOAN, 'N' AS CHUOI, DAO, NULL AS DAO_NAME, BRANCH_CODE, BRANCH_NAME_SME, ZONE_ID_SME
FROM TBL_TTQT_DAILY
GROUP BY CIF, CUSTOMER_NAME,DAO, BRANCH_CODE, BRANCH_NAME_SME, ZONE_ID_SME) AS A, TBL_CM_TRADE B
WHERE A.CIF=B.CIF

UPDATE TBL_CM_TRADE
SET BAL_BL = A.BAL_BL
FROM 
(SELECT CUST_ID, SUM(AMOUNT_QD) AS BAL_BL
FROM DOANH_SO_BAO_LANH BL, TBL_CUSTOMER CUST
WHERE BL.CUST_ID = CUST.CIF
GROUP BY CUST_ID) AS A, TBL_CM_TRADE B
WHERE A.CUST_ID=B.CIF

DECLARE @DATE_END_MONTH NVARCHAR(100)= (SELECT [MAX_DATE_ENDMONTH] FROM [DATE_EM_REPORT])
--PRINT @DATE_END_MONTH

UPDATE TBL_CM_TRADE
SET NEW_CUST_STATUS = 1
 FROM SERVER12.ANHCP.DBO.KH_TRADE_MOI A, TBL_CM_TRADE B
 WHERE A.CUSTOMER_ID = B.CIF AND  A.FROM_DATE > @DATE_END_MONTH
 
-- DELETE FROM [DBCUSTOMER].DBO.TBL_CM_TRADE
--INSERT INTO [DBCUSTOMER].DBO.TBL_CM_TRADE
--SELECT * FROM TBL_CM_TRADE

-- EXEC('IF EXISTS
--      (SELECT *
--         FROM [SERVER12].[SMECUSTOMER360].DBO.SYSOBJECTS O
--        WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_CM_TRADE'')
--        DELETE FROM [SERVER12].[SMECUSTOMER360].DBO.TBL_CM_TRADE')
 
--INSERT [SERVER12].[SMECUSTOMER360].DBO.TBL_CM_TRADE
--SELECT * FROM TBL_CM_TRADE

select * from [SERVER12].[SMECUSTOMER360].DBO.TBL_CM_TRADE